﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Wedo.Recognition.Domain;
using Wedo.Recognition.Domain.Models;
using System.Web.Mvc;
using System.Data.Entity;
using Wedo.Recognition.Domain.ViewModels;
using Wedo.Domain.Models; 
namespace Wedo.Recognition.Service.Service
{
    public class CustomerService
    {

        private DbContext MDMdbContext;

        public CustomerService( )
        {
            MDMdbContext = new DbContext("name=MDMDbContext");
        }

        public List<Customer_Sap> GetCustomerList(string customerCode, string customerName)
        {
            string sql = "select top 100 * from  Customer_Sap where Other7='Approved'  and isnull( applydepartment,'') <> 'CCG'";


            if (!string.IsNullOrWhiteSpace(customerCode))
                sql += " and CustomerCode like '%"+customerCode+"%' "; 

            if (!string.IsNullOrWhiteSpace(customerName))
                sql += " and CompanyName like N'%" + customerName + "%' ";

            return  MDMdbContext.Database.SqlQuery<Customer_Sap>(sql).ToList();

        }

        public List<SelectListItem> GetCustomerList(string keyword)
        {
            string sql = "select top 100 CustomerCode Value,CompanyName Text  from  Customer_Sap where Other7='Approved'  and isnull( applydepartment,'') <> 'CCG'";


            if (!string.IsNullOrWhiteSpace(keyword))
                sql += " and( CustomerCode like '%" + keyword + "%' or CompanyName like N'%" + keyword + "%' )";

            return MDMdbContext.Database.SqlQuery<SelectListItem>(sql).ToList();
        }


        public List<string> GetCustomerCodelist()
        {
            string sql = "select CustomerCode from Customer_Sap where Other7='Approved'  and isnull( applydepartment,'') <> 'CCG'";
            return MDMdbContext.Database.SqlQuery<string>(sql).ToList();

        }

        public string GetCustomNameByCode(string customerCode)
        {
            string sql = "select CompanyName from  Customer_Sap where Other7='Approved'  and isnull( applydepartment,'') <> 'CCG' and CustomerCode='" + customerCode + "' ";
            return MDMdbContext.Database.SqlQuery<string>(sql).FirstOrDefault();

        }

        /// <summary>
        /// 获取默认的用户
        /// 第一位是CustomerCode
        /// 第二位是CompanyName
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public string[] GetDefaultCustomer(string name)
        {
            string sql = "select top 1 CustomerCode+','+CompanyName from  Customer_Sap where Other7='Approved' and isnull( applydepartment,'') <> 'CCG'";
            if (name.IsNotNullOrWhiteSpace())
            {
                string sqlformat = "(isnull({1},'')<>'' and {1}=N'{0}')";
                sql += " and (" + string.Format(sqlformat, name, "CompanyName");
                sql += " or " + string.Format(sqlformat, name, "CompanyNameEn") + ")";

            }
            else
                return null;
            var values= MDMdbContext.Database.SqlQuery<string>(sql).FirstOrDefault();
            if (values != null)
            {
                return values.Split(new char[] { ',' }, 2, StringSplitOptions.RemoveEmptyEntries);
            }
            return null;
        }

        /// <summary>
        /// 部门
        /// </summary>
        /// <returns></returns>
        public List<string> GetHRDepartMent()
        {
            DbContext BPMdbContext = new DbContext("name=BPMDbContext");
            string sql = @"select distinct department from cm_employees where department is not null order by department";
            return BPMdbContext.Database.SqlQuery<string>(sql).ToList();
        }



        public List<ShortEmployee> GetEmployeeByDepartment(string hrdepartmentname)
        {
            DbContext BPMdbContext = new DbContext("name=BPMDbContext");

            string sql = @"select [StaffCode]
                                  ,[StaffNameEN] StaffName
                                  ,[Inner_Email]
                                  ,[ADAccount]
            from  cm_employees e where e.Department=N'" + hrdepartmentname + "' and department is not null order by staffcode";

            return BPMdbContext.Database.SqlQuery<ShortEmployee>(sql).ToList();

        }

        public List<UserInfo> GetUserInfos(string[] codes)
        {
            if (codes == null || codes.Length == 0)
            {
                throw new Exception("必须选择一条数据");
            }

            DbContext BPMdbContext = new DbContext("name=BPMDbContext");
            string staffCodeWhere = "[StaffCode] in('" + string.Join("','", codes) + "') ";

            string selectSql = @"select newid() Id,[CompanyNo],'' as Password
                                            ,Department
                                            ,[Division]
                                            ,[StaffCode]
                                            ,[StaffNameCN]
                                            ,[StaffNameEN]
                                            ,[CostCenter]
                                            ,[WorkCenter]
                                            ,[BirthDay]
                                            ,[Gender]
                                            ,[Phone]
                                            ,[Mobile]
                                            ,[Inner_Email]
                                            ,[Exter_Email]
                                            ,[iVantage_Code]
                                            ,[Manager]
                                            ,[ManagerName]
                                            ,[ADAccount] Name
                                            ,[Manual]
                                            ,[IsCLT]
                                            ,'admin' as CreatedBy
                                            ,GETDATE() Created
                                            ,'admin' as ModifiedBy
                                            ,GETDATE() Modified
                                            ,[EmployDate]
                                            ,[DimissionDate]
                                            ,[EmployeeType]
                                        from [BPM].[dbo].[CM_Employees]
                                        where  [EmployeeType]!='Z' and " + staffCodeWhere;

            return BPMdbContext.Database.SqlQuery<UserInfo>(selectSql).ToList();
        }
    }
}
